Final Exam: Data Analyst

Final Exam: Data Analyst

• add a button to a workbook to display a complex user form and demonstrate the resulting fully-fledged user application from Excel
• add user forms to an Access database and configure various user controls
• apply a brute-force approach to find the optimal model for your dataset
• apply basic data manipulation operations on DataFrames
• apply the loc and iloc functions to access specific rows and columns
• cast data types within Series objects
• compare the performance of a small ensemble with a larger one
• compute aggregations on data
• create a dataset out of a data source and analyze the different fields in the data
• create a pandas Series object
• create macros
• create models from your cluster instances to identify the factors which affect cluster membership
• create pandas Series objects
• customize Excel menus to display developer features
• demonstrate the use of MS-Access, a lightweight relational database, and create a sample database (.acc) file and a table
• describe the features and use cases of linear regression
• describe the features of clustering models which can be configured
• describe the process in which a machine learning model is constructed using training data
• describe the process of preparing a dataset for logistic regression
• edit the contents of a range of cells using direct cell references
• filter data using the iloc function
• filter data using the loc, iloc, at, and iat functions
• generate clusters in your input data and analyze the properties of each cluster
• identify and deal with duplicate records
• identify the features of clustering models which can be configured
• illustrate the use of MS-Access, a lightweight relational database, and create a sample database (.acc) file and a table
• import and export data in CSV files
• import and export data using HTML and JSON files
• import data from a CSV file
• introduce user forms as a way to add complex UIs to an Excel workbook and use the VBA forms control toolbox to add elements, such as buttons, to a user form
• load data from a variety of sources into BigML to train
• load data from a variety of sources into BigML to train and evaluate machine learning models
• lookup data using different techniques
• navigate Visual Basic editor
• organize your BigML resources such as data sources, datasets and models into projects
• perform a brute-force approach to find the optimal model for your dataset
• perform basic data manipulation operations on DataFrames
• perform basic operations on Series objects
• perform common grouping and aggregation operations
• perform inner join operations using the merge() method
• recall the various metrics used to evaluate the quality of a machine learning model
• recognize the features available in BigML to load data and to train a machine learning model
• recognize the properties of ensemble models which can be configured in BigML
• recognize the purpose of clustering algorithms and list some of its use cases
• recognize the types of machine learning algorithms and their applications
• run SQL queries, such as SELECT-FROM-WHERE queries, to analyze data in MS Access
• serialize data to Excel and Pickle files
• specify the rows in a table as well as a primary key, import data from a CSV file into this table, and recognize how SQL queries work in MS Access
• summarize records into bins or categories
• train a logistic regression model to predict an output based on the probability of occurrence
• use BigML to build an ensemble of decision trees to solve a classification problem
• use inner join operations using the merge() method
• use relative references while recording a macro and recognize how it affects the output, accept user input using the InputBox function, and insert sheets into a Workbook from VBA
• use shortcut keys to navigate Visual Basic Editor
• use SQL INSERT statements to add rows to the Access database based on user input to a form
• use the loc and iloc functions to access specific rows and columns
• use VBA macros to autofit rows and columns
• use VBA's support for sending emails from within macros
• wire up a button to VBA code so that whenever that button is clicked, a message is displayed, design a fully-fledged user form to accept complex user input using input boxes, and configure buttons to submit or reset that user input
• wire up a complex user form to store data in an Excel workbook

Final Exam: Data Analyst will test your knowledge and application of the topics presented throughout the Data Analyst track of the Skillsoft Aspire Business Analyst to Data Analyst Journey.

VBA: Building User Interfaces with Forms in VBA & Excel

VBA: Building User Interfaces with Forms in VBA & Excel

• discover the key concepts covered in this course
• introduce user forms as a way to add complex UIs to an Excel workbook and use the VBA forms control toolbox to add elements, such as buttons, to a user form
• wire up a button to VBA code so that whenever that button is clicked a message is displayed, design a fully-fledged user form to accept complex user input using input boxes, and configure buttons to submit or reset that user input
• wire up a complex user form to store data in an Excel workbook
• add a button to a workbook to display a complex user form and demonstrate the resulting fully-fledged user application from Excel
• use VBA's support for sending emails from within macros, enable and use Microsoft CDO (Collaboration Data Objects), recognize security flaws for sending emails from VBA, and identify the setup required if Gmail is the mail provider
• develop a complex user form and add complex controls such as dropdown menus (known as combo boxes in VBA), checkboxes, radio buttons, and spin buttons
• add complex controls to an app, including spin buttons and checkboxes, create a close button to unload the form's memory and cease displaying it, and add a submit button to store user input in a workbook
• wire up the back-end code to process user inputs from the form, create a display button to show the form, and demonstrate the resulting fully-fledged user application from Excel
• summarize the key concepts covered in this course

One of the many capabilities of Excel's VBA is building complex user interfaces within Excel. Part of this is the creation of complex forms for gathering input data. In this course, you'll learn how to create user forms using VBA in Excel and, in doing so, achieve user interfaces with complex controls like combo boxes and spinners. You'll also learn how to send emails from VBA while being mindful of security risks and the configuration required if the email provider is Gmail. You'll learn how to validate form input data, insert it into an Excel spreadsheet, and illustrate how such user input can be accepted using complex controls, such as radio buttons and checkboxes, and buttons that, when clicked, trigger the invocation of VBA subroutines.

VBA: Getting Started with VBA in Excel

VBA: Getting Started with VBA in Excel

• discover the key concepts covered in this course
• customize Excel menus to display developer features, enter the VBA console, identify the default macro settings in the trust center, create an auto-open macro, display a message-box from VBA, associate the auto-open macro with an Excel workbook, and save that workbook as a macro-enabled (.xlsm) workbook
• create an auto-close macro, contrast the working of the auto-open and auto-close macros, compare functions and sub-routines in VBA, create a function that accesses the user name using the Excel object model, invoke that function from a worksheet cell, access and modify the contents of the active cell from VBA, use cell references with cell addresses from VBA, and invoke subroutines using the Macros>Run menu
• edit the contents of a range of cells using direct cell references, an object of the Range class, and ActiveSheet.Range(), Sheet().Range(), and ActiveSheet
• use VBA macros to autofit rows and columns, toggle the word-wrap setting of a range of cells, create a new macro using the Macro Recording feature, assign a keyboard shortcut to a recorded macro, and invoke that macro using the macro pane and keyboard shortcut
• use relative references while recording a macro and recognize how it affects the output, accept user input using the InputBox function, and insert sheets into a Workbook from VBA
• insert rows and columns from VBA, understand how errors in macros are displayed, and debug an error encountered while running a VBA macro
• merge cells and center them and hide and unhide rows and columns
• implement conditional formatting using VBA with a FormatConditions object, select all cells in a range that satisfy a certain condition, apply specific formatting to those cells, change the text and background color of cells, add buttons to an Excel workbook and associate them with specific macros
• perform conditional formatting operations using VBA and simulate the use of arrow keys from within VBA using xlDown to go from the active cell to the last entry in a contiguous range
• format a range of cells without using a format conditions object and recognize how removing formatting using a FormatConditions object will only remove formatting formatted using a FormatConditions object
• insert a formula into a worksheet cell using absolute references from VBA, identify the limitations of xlDown when used without a clearly defined contiguous range around it, and use relative cell references and the size of a range to mitigate these limitations
• summarize the key concepts covered in this course

Excel's VBA can be a powerful tool useful for a multitude of purposes if you know how to leverage its capabilities, debug issues, and mitigate for specific limitations. In this introductory course, you'll begin by using subroutines in VBA to perform operations. You'll then define functions and reference and edit cell ranges and Excel sheets with VBA. After that, you'll invoke subroutines with relative cell references, record macros in Excel, and debug macros in VBA. You'll insert columns and sheets from VBA and format cells based on a condition in VBA both manually and using a FormatConditions object. Finally, you'll illustrate how clearing formatting using a FormatConditions object will only clear formatting created using a FormatConditions object, not by using if-else conditionals.

VBA: Leveraging VBA to Work with Charts, Stocks, & MS Access

VBA: Leveraging VBA to Work with Charts, Stocks, & MS Access

• discover the key concepts covered in this course
• use VBA to create a pivot table and work with that pivot table both through VBA and directly via Excel
• use VBA to create a simple chart on a new sheet and manipulate the data that it contains
• change the chart type, format the chart to use 2-D as well as 3-D chart types from VBA, resize it, and save it as an image
• work with Excel's Stock Data feature, which pulls in stock prices for specific financial assets
• use VBA to automate the fetching of financial data and demonstrate the use of named ranges from within a VBA macro
• illustrate the use of MS-Access, a lightweight relational database, and create a sample database (.acc) file and a table
• specify the rows in a table as well as a primary key, import data from a CSV file into this table, and recognize how SQL queries work in MS Access
• run SQL queries, such as SELECT-FROM-WHERE queries, to analyze data in MS Access
• combine MS Access and VBA, add user forms to an Access database, configure various user controls, such as buttons and input boxes, and configure a calculated column based on an expression value
• use SQL INSERT statements to add rows to the Access database based on user input to a form
• demonstrate the use of event handlers in Excel, create a VBA subroutine to be executed when the user attempts to save a workbook, and add similar handlers for events, such as opening or closing a workbook
• create an event handler for activating a worksheet, insert a new worksheet, and change the contents of a cell
• summarize the key concepts covered in this course

Leveraging VBA with Excel has many useful capabilities, including the creation and management of pivot tables and charts, and the automation of tasks in MS Access, the lightweight relational database.

In this course, you'll learn how to create and edit pivot tables and different chart types, such as bar charts and line charts, using VBA in Excel. You'll then save these Excel charts as images, again using VBA. Next, you'll use Excel's stock datatype to access financial data.

Moving on, you'll learn how to use VBA to automate tasks in MS Access, accept user input, validate it, and insert it into an MS Access database. You'll also learn how to run simple SQL queries against that database. Finally, you'll set up event handlers in Excel using VBA and illustrate their purpose.